# _*_ coding:utf-8 _*_
# @Time  : 2022.07.13
# @Author: zizlee

# 非常规工作
import json
import pathlib
import datetime
from collections import Counter
from typing import Optional
from fastapi import APIRouter, UploadFile, Form, Header, Query, Body
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel, ValidationError, conint, validator, datetime_parse

from db import FAConnection
from hutool import security
from v1_all_api import constant
from v1_all_api.all_utils import file_utils, datetime_utils
from v1_all_api.all_response import AllResponse
from configs import FILE_STORAGE
from . import work_utils

special_task_api = APIRouter()


@special_task_api.get('/')  # 查询非常规工作记录
def query_special_task(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                       page: int = Query(1, ge=1), page_size: int = Query(10, ge=5), user: int = Query(0, ge=0)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    user_id = 'NOUSER' if not user else user
    query_sql = """
        SELECT a.id,a.create_date,a.user_id,a.title,a.task_type,a.sponsor,a.applicant,a.phone,a.swiss_coin,a.allowance,
            a.partner,a.score,a.annex,a.annex_url,a.is_examined,b.ruizy_code,b.admin_name   
        FROM work_special_task As a 
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.create_date>=%s AND a.create_date<=%s 
        AND IF('NOUSER'=%s,TRUE,a.user_id=%s) 
        ORDER BY create_date DESC,id DESC;
    """
    query_prm = [start_date, end_date, user_id, user]
    db_conn = FAConnection()
    ret = db_conn.query_paginator(query_sql, param=query_prm, page=page, page_size=page_size)
    return AllResponse.operate_successfully(data=ret)


class PutSpecialTaskItem(BaseModel):
    tid: conint(ge=1)
    is_examined: conint(ge=0, le=1)
    score: conint(ge=0, le=5)


@special_task_api.put('/')  # 管理员保存修改的记录
def save_special_task(ruizy_token: str = Header(...), task_item: PutSpecialTaskItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    # 修改数据
    sql = "UPDATE work_special_task SET score=%s,is_examined=%s WHERE id=%s LIMIT 1;"
    db_conn = FAConnection()
    _, success = db_conn.execute(sql, param=(task_item.score, task_item.is_examined, task_item.tid))
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class SpecialTaskItem(BaseModel):
    create_date: datetime_parse.date
    title: str
    task_type: str
    sponsor: str
    applicant: str
    phone: str
    swiss_coin: int
    allowance: float
    partner: str
    note: str

    @validator('task_type')
    def validate_task_type(cls, value):
        if value not in constant.SPECIAL_TASK_TYPES.keys():
            raise ValidationError('task_type not allowed!')
        return value


@special_task_api.post('/my/')  # 添加一个非常规工作记录
async def create_special_task(ruizy_token: str = Header(...), annex_file: UploadFile = Form(None),
                              body_data: str = Form(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    try:
        body_content = json.loads(body_data)
        body_item = SpecialTaskItem(**body_content)
    except json.decoder.JSONDecodeError:
        return AllResponse.validate_error(msg='parse body_data Error,it must be JSON string')
    except ValidationError as e:
        return AllResponse.validate_error(msg='参数错误！{}'.format(e))
    if not all([body_item.title, body_item.task_type]):
        return AllResponse.validate_error(msg='标题和类型为必填项！')
    task_obj = jsonable_encoder(body_item)
    task_obj['create_date'] = body_item.create_date.strftime('%Y%m%d')
    task_obj['user_id'] = person['uid']
    task_obj['score'] = 5
    task_obj['annex'] = ''
    task_obj['annex_url'] = ''
    save_path = None
    if annex_file:  # 有附件
        save_path, sql_path = file_utils.get_file_paths(module_name='SpecialTask', filename=annex_file.filename,
                                                        hashed=True)
        # 保存附件到指定文件夹
        file_content = await annex_file.read()
        with open(save_path, 'wb') as fp:
            fp.write(file_content)
        await annex_file.close()
        task_obj['annex'] = annex_file.filename
        task_obj['annex_url'] = sql_path
    # 保存记录到数据库
    sql = """
        INSERT INTO work_special_task (create_date,user_id,title,task_type,sponsor,applicant,phone,swiss_coin,allowance,
        partner,note,score,annex,annex_url) VALUES (%(create_date)s,%(user_id)s,%(title)s,%(task_type)s,%(sponsor)s,
        %(applicant)s,%(phone)s,%(swiss_coin)s,%(allowance)s,%(partner)s,%(note)s,%(score)s,%(annex)s,%(annex_url)s);
    """
    db_conn = FAConnection()
    success, _ = db_conn.insert(sql, param=task_obj)
    if not success and save_path:
        annex = pathlib.Path(save_path)
        if annex.is_file():
            annex.unlink()
    return AllResponse.create_successfully() if success else AllResponse.server_error()


@special_task_api.get('/my/')  # 获取我的非常规工作记录
def query_special_task(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    sql = """
        SELECT id,create_date,title,task_type,sponsor,applicant,phone,swiss_coin,allowance,partner,note,score,
            annex,annex_url,is_examined 
        FROM work_special_task 
        WHERE user_id=%s AND create_date>=%s AND create_date<=%s 
        ORDER BY create_date DESC,id DESC;
    """
    prm = (person['uid'], start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    # 统计一下当前月的数据
    today = datetime.datetime.today()
    month_start, month_end = datetime_utils.month_start_end(year=today.year, month=today.month, rf='%Y%m%d')
    current_month_count = 0
    for task in records:
        task['task_type_zh'] = constant.SPECIAL_TASK_TYPES.get(task['task_type'], '')
        task['examined_zh'] = '是' if task['is_examined'] else '否'
        if month_start <= str(task['create_date']) <= month_end:
            current_month_count += 1
    response_data = {
        'data': records,
        'count':  current_month_count
    }
    return AllResponse.operate_successfully(data=response_data)


class DeleteParamItem(BaseModel):
    start: Optional[datetime_parse.date]
    end: Optional[datetime_parse.date]
    tid: Optional[int]


@special_task_api.delete('/my/')  # 用户删除非常规工作
def delete_user_task(ruizy_token: str = Header(...), param: DeleteParamItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 之删除一个，不允许批量删除了
    if not param.tid:
        return AllResponse.validate_error(msg='参数错误!')
    query_annex = "SELECT annex_url FROM work_special_task WHERE id=%s AND user_id=%s AND prse_id>0;"  # prse_id>0协同服务添加过过来的不能删除
    sql = "DELETE FROM work_special_task WHERE id=%s AND user_id=%s;"
    prm = (param.tid, person['uid'])
    with FAConnection() as cursor:
        cursor.execute(query_annex, prm)
        annex_obj = cursor.fetchone()

        c = cursor.execute(sql, prm)
        if c and annex_obj:
            annex_file = pathlib.Path(FILE_STORAGE).joinpath(annex_obj['annex_url'])
            if annex_file.exists() and annex_file.is_file():
                annex_file.unlink()
    rep_msg = '操作成功，删除了{}条数据!'.format(c)
    return AllResponse.operate_successfully(msg=rep_msg)


@special_task_api.get('/statistics/')  # 非常规工作记录统计
def statistics_special_task(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                            st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    sql = """
        SELECT a.create_date,a.id,a.user_id,a.score,a.is_examined,a.title,a.applicant,a.swiss_coin,a.score,
            a.task_type,a.annex,a.annex_url,a.prse_id,a.prse_module,b.admin_name 
        FROM work_special_task As a
        INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.create_date>=%s AND a.create_date<=%s 
        ORDER BY a.create_date DESC;
    """
    prm = (start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm, keep_conn=True)

    # 查询路演中的营业部数据
    lecture_sql = """
        SELECT a.id,a.user_id,a.create_date,a.title,a.applicant,a.lecture_type,a.lecture_scale,a.effect_flag,
            a.annex,a.annex_url,a.level_flag,a.swiss_coin,b.admin_name  
        FROM work_lecture As a INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.is_active=1 AND a.create_date>=%s AND a.create_date<=%s AND a.applicant='营业部';
    """
    lecture_records = db_conn.query(lecture_sql, param=prm)

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['user_id'] in members_id, records))
            lecture_records = list(filter(lambda x: x['user_id'] in members_id, lecture_records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['user_id'] in members_id, records))
        lecture_records = list(filter(lambda x: x['user_id'] in members_id, lecture_records))

    user_dict = {}
    total_effective_count = 0
    levels = {5: 'A', 4: 'B', 3: 'C', 2: 'D', 1: 'E'}
    for task in records:
        task['task_type_zh'] = constant.SPECIAL_TASK_TYPES.get(task['task_type'])
        task['level_flag'] = levels.get(task['score'], '')
        if task.get('prse_module'):
            task['prse_name'] = work_utils.get_module_name(task['prse_module'])
        if task['user_id'] in user_dict.keys():
            user_data = user_dict[task['user_id']]
            user_data['total_count'] += 1
            if task['is_examined']:
                user_data['levels'].append(task['level_flag'])  # 有效的才统计评级
                user_data['effective_count'] += 1
                total_effective_count += 1
            else:
                user_data['invalid_count'] += 1
        else:
            total_count = 1
            effective_count = 0
            invalid_count = 0
            task_levels = []
            if task['is_examined']:
                effective_count = 1
                total_effective_count += 1
                task_levels.append(task['level_flag'])
            else:
                invalid_count = 1
            user_dict[task['user_id']] = {
                'user_id': task['user_id'],
                'admin_name': task['admin_name'],
                'levels': task_levels,
                'total_count': total_count,
                'effective_count': effective_count,
                'invalid_count': invalid_count,
                'lecture_levels': [],  # 路演
                'lecture_count': 0  # 路演记录
            }
    # 统计营业部的路演
    for lec in lecture_records:
        if lec['user_id'] in user_dict.keys():
            user_obj = user_dict[lec['user_id']]
            user_obj['total_count'] += 1
            user_obj['lecture_levels'].append(lec['level_flag'])
            user_obj['lecture_count'] += 1
        else:
            user_dict[lec['user_id']] = {
                'user_id': lec['user_id'],
                'admin_name': lec['admin_name'],
                'levels': [],
                'total_count': 1,
                'effective_count': 0,
                'invalid_count': 0,
                'lecture_levels': [lec['level_flag']],
                'lecture_count': 1
            }

    statistics_data = []
    # 合计列使用的level
    total_levels = []
    efc, inc, ttc, ltc = 0, 0, 0, 0
    for _, v in user_dict.items():
        # 202208830总数量前端要显示有效的，所以总数量=总数量-无效量
        v['total_count'] = v.get('total_count', 0) - v.get('invalid_count', 0)
        efc += v['effective_count']  # 合计的有效
        inc += v['invalid_count']  # 合计的无效
        ttc += v['total_count']  # 合计总量
        if v['lecture_count']:
            ltc += v['lecture_count']  # 路演合计数量
        else:
            v['lecture_count'] = None
        if not v['total_count']:
            del v['total_count']
        v['levels'].sort()
        total_levels.extend(v['levels'])
        level_count = Counter(v['levels']).items()
        v['level_count'] = ''.join(['%d%s' % (v, k) for k, v in level_count if k])
        del v['levels']
        # 计算路演的
        v['lecture_levels'].sort()
        lecture_level_count = Counter(v['lecture_levels']).items()
        v['lecture_level_count'] = ''.join(['%d%s' % (v, k) for k, v in lecture_level_count if k])

        statistics_data.append(v)
    statistics_data.sort(key=lambda x: x.get('total_count', 0), reverse=True)  # 按总量排序
    # 加入合计列
    if len(statistics_data) > 0:
        total_levels.sort()
        total_levels = ''.join(['%d%s' % (v, k) for k, v in Counter(total_levels).items() if k])
        lecture_levels = [''.join(i['lecture_level_count']) for i in statistics_data if i['lecture_levels']]
        statistics_data.append({
            'user_id': -1,
            'admin_name': '合计',
            'level_count': total_levels,
            'total_count': ttc if ttc else None,
            'effective_count': efc,
            'invalid_count': inc,
            'lecture_count': ltc if ltc else None,
            'lecture_level_count': work_utils.calculate_levels_sum(lecture_levels)
        })
    for st in statistics_data:
        for c in ['effective_count', 'invalid_count']:
            if not st[c]:
                st[c] = None
    response_data = {
        'statistics': statistics_data,
        'records': records
    }
    return AllResponse.operate_successfully(data=response_data)


@special_task_api.get('/statisticsMonthly/')  # 按月份统计非常规(含路演营业部类型数据)
def statistics_task_monthly(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                            st: int = Query(1, ge=1, le=2)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='param start and end format error!')
    is_admin = security.operator_is_manager(person)
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    sql = """
            SELECT a.create_date,a.id,a.user_id,a.score,a.is_examined,a.title,a.applicant,a.swiss_coin,a.score,
                a.task_type,a.annex,a.annex_url,b.admin_name 
            FROM work_special_task As a
            INNER JOIN ruizy_user As b ON a.user_id=b.id 
            WHERE a.create_date>=%s AND a.create_date<=%s AND a.is_examined=1 
            ORDER BY a.create_date DESC;
        """
    prm = (start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm, keep_conn=True)

    # 查询路演中的营业部数据
    lecture_sql = """
        SELECT a.id,a.user_id,a.create_date,a.title,a.applicant,a.lecture_type,a.lecture_scale,a.effect_flag,
            a.annex,a.annex_url,a.level_flag,a.swiss_coin,b.admin_name  
        FROM work_lecture As a INNER JOIN ruizy_user As b ON a.user_id=b.id 
        WHERE a.is_active=1 AND a.create_date>=%s AND a.create_date<=%s AND a.applicant='营业部';
    """
    lecture_records = db_conn.query(lecture_sql, param=prm)
    task_records = records + lecture_records

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            task_records = list(filter(lambda x: x['user_id'] in members_id, task_records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        task_records = list(filter(lambda x: x['user_id'] in members_id, task_records))

    month_list = datetime_utils.month_of_range(start=start_date, end=end_date, ft='%Y%m%d')
    # 对数据按人员按月份统计
    statistics_data, table_columns = work_utils.statistics_monthly(task_records, month_list=month_list)
    response_data = {
        'statistics_data': statistics_data,
        'table_columns': table_columns
    }
    return AllResponse.operate_successfully(data=response_data)


# 删除非常规工作(管理员)
@special_task_api.delete('/manage/')
def delete_lecture_record(ruizy_token: str = Header(...), rid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    # 删除记录
    with FAConnection() as cursor:
        # 查询删除附件
        cursor.execute('SELECT id,annex_url,prse_id FROM work_special_task WHERE id=%s LIMIT 1;', [rid])
        obj = cursor.fetchone()
        if not obj:
            return AllResponse.validate_error(msg='删除的记录不存在!')
        annex_file = pathlib.Path(FILE_STORAGE).joinpath(obj['annex_url'])
        c = cursor.execute('DELETE FROM work_special_task WHERE id=%s LIMIT 1;', [rid])
        # prse_id==0的时候才能删除附件，否则协同服务里的附件会被删除
        if c > 0 and obj['prse_id'] == 0 and annex_file.exists() and annex_file.is_file():
            annex_file.unlink()
    return AllResponse.operate_successfully()
